from sqlalchemy import Column, String, Text, Integer, BigInteger, Float, \
    DateTime, Date, ForeignKey, create_engine, and_, or_;
from sqlalchemy.orm import sessionmaker, relationship;
from sqlalchemy.ext.declarative import declarative_base;

'''
sqlalchemy 工具组件
'''

# 获得基础base类
Base = declarative_base();

# 初始化数据库引擎
def init_db_engin():
    engin = None;
    try:
        # mysql+mysqlconnector://root:root@127.0.0.1:3306/main?auth_plugin=mysql_native_password
        engin = create_engine("mysql+mysqlconnector://root:root@127.0.0.1:3306/wgd?auth_plugin=mysql_native_password");
    except BaseException as e:
        print(e);
    return engin;

# 初始化session对象
def init_db_session():
    engin = init_db_engin();
    if engin:
        session = sessionmaker(bind=engin);
        return session();
    else:
        return None;

# 初始化表格
def init_table():
    engin = init_db_engin();
    if engin:
        Base.metadata.create_all(bind=engin);

'''
新增函数，传入需要操作的对象，并以某一属性作为判断条件，如果数据库已经存在则不操作
'''
def insert_(entity, property):
    session = init_db_session();
    if not session:
        return;
    try:
        '''
        实例，根据用户名判断是否插入新的数据
        查询数据库 user 表，过滤所有用户名相同的数据
        如果过滤结果长度大于0，则表明该用户名已经存在，则不插入
        如果过滤结果等于0，则表明用户名没有重复，可以插入
        '''
        results = session.query(type(entity)).filter(
            type(entity).__dict__.get(property) == entity.__dict__.get(property)
        ).all();
        if len(results) == 0:
            session.add(entity);
            session.commit();
        else:
            print("%s 已经存在" % property);
    except BaseException as e:
        print(e);
        session.rollback();
    finally:
        session.close();

# update
def update_(entity, property):
    d = entity.__dict__;
    d.pop("_sa_instance_state");

    session = init_db_session();
    if not session:
        return;

    try:
        session.query(type(entity)).filter(
            type(entity).__dict__.get(property) == entity.__dict__.get(property)
        ).update(d);
        session.commit();
    except BaseException as e:
        print(e);
        session.rollback();
    finally:
        session.close();

# delete
def delete_(entity, property):
    session = init_db_session();
    if not session:
        return;

    try:
        session.query(type(entity)).filter(
            type(entity).__dict__.get(property) == entity.__dict__.get(property)
        ).delete();
        session.commit();
    except BaseException as e:
        print(e);
        session.rollback();
    finally:
        session.close();

# get
def get_(entity, property):
    session = init_db_session();
    if not session:
        return;

    result = None;
    try:
        result = session.query(type(entity)).filter(
            type(entity).__dict__.get(property) == entity.__dict__.get(property)
        ).first();
    except BaseException as e:
        print(e);
        session.rollback();
    finally:
        session.close();

    return result;

# get all
def get_all_(entity):
    session = init_db_session();
    if not session:
        return;

    result = None;
    try:
        result = session.query(type(entity)).all();
    except BaseException as e:
        print(e);
        session.rollback();
    finally:
        session.close();

    return result;

# 执行原生 sql
def execute_(sql):
    results = None;
    session = init_db_session();
    if session:
        if sql.lower().startswith("select"):
            results = session.execute(sql).fetchall();
        else:
            results = session.execute(sql);
            session.commit();
        session.close();
    return results;